<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Zipcode_model extends CI_Model {

	function __construct() {
		parent::__construct();
	}

	/**
	 * 우편번호 검색
	 */
	function select_zipcode_list($dong, $page_no, $page_size) {
		log_message('debug',"#### Zipcode_model::select_zipcode_list($dong, $page_no, $page_size)");

		$start_row = ($page_no-1) * $page_size;

		$sql  = PHP_EOL." SELECT zipcode, TRIM(CONCAT_WS(' ', address1, address2, address3, address4, address5, address7)) addr, address_full ";
		$sql .= PHP_EOL." FROM  tb_zipcode ";
		$sql .= PHP_EOL." WHERE address3 LIKE '$dong%' ";
		$sql .= PHP_EOL." OR    address4 LIKE '$dong%' ";
		$sql .= PHP_EOL." ORDER BY address_full ";
		$sql .= PHP_EOL." LIMIT $start_row , $page_size ";

        $query = $this->db->query($sql) or $this->logsql->log_sql('error');
		$result = $query->result_array();

		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	/**
	 * 우편번호 검색 총 내역 수
	 */
	function get_zipcode_total_count($dong) {
		log_message('debug',"#### Zipcode_model::get_zipcode_total_count($dong)");

		$sql  = PHP_EOL." SELECT COUNT(*) cnt ";
		$sql .= PHP_EOL." FROM  tb_zipcode ";
		$sql .= PHP_EOL." WHERE address3 LIKE '$dong%' ";
		$sql .= PHP_EOL." OR    address4 LIKE '$dong%' ";

        $query = $this->db->query($sql) or $this->logsql->log_sql('error');
		$result = $query->row_array();

		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result['cnt'];
	}

	/**
	 * 우편번호 검색 (도로명 검색)
	 */
	function select_zipcode_street_list($city, $street, $page_no, $page_size) {
		log_message('debug',"#### Zipcode_model::select_zipcode_street_list($city, $street, $page_no, $page_size)");

		$start_row = ($page_no-1) * $page_size;

		$sql  = PHP_EOL." SELECT A.*, CONCAT(A.addr,' ',A.address7) address_full ";
		$sql .= PHP_EOL." FROM ( ";
		$sql .= PHP_EOL."         SELECT ";
		$sql .= PHP_EOL."               zipcode ";
		$sql .= PHP_EOL."             , address1 ";
		$sql .= PHP_EOL."             , REPLACE(TRIM(CONCAT_WS(' ', address1, address2, address3, address4, IF(address6 > '0', CONCAT(address5,'-',address6), address5))),'  ',' ') addr ";
		$sql .= PHP_EOL."             , address7 ";
		$sql .= PHP_EOL."             , (SELECT address_full FROM tb_zipcode WHERE zipcode = S.zipcode AND id = S.id) address_dong ";
		$sql .= PHP_EOL."         FROM  tb_zipcode_street S ";
		$sql .= PHP_EOL."         WHERE address4 LIKE '$street%' ";
		$sql .= PHP_EOL."         OR    address7 LIKE '$street%' ";
		$sql .= PHP_EOL." ) A ";
		$sql .= PHP_EOL." WHERE A.address1 LIKE '$city%' ";
		$sql .= PHP_EOL." ORDER BY A.zipcode, address_full ";
		$sql .= PHP_EOL." LIMIT $start_row , $page_size ";

        $query = $this->db->query($sql) or $this->logsql->log_sql('error');
		$result = $query->result_array();

		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}
	
	/**
	 * 우편번호 검색 (도로명 검색) 총 내역 수
	 */
	function get_zipcode_street_total_count($city, $street) {
		log_message('debug',"#### Zipcode_model::get_zipcode_street_total_count($city, $street)");

		$sql  = PHP_EOL." SELECT COUNT(*) cnt ";
		$sql .= PHP_EOL." FROM ( ";
		$sql .= PHP_EOL."         SELECT address1 ";
		$sql .= PHP_EOL."         FROM  tb_zipcode_street ";
		$sql .= PHP_EOL."         WHERE address4 LIKE '$street%' ";
		$sql .= PHP_EOL."         OR    address7 LIKE '$street%' ";
		$sql .= PHP_EOL." ) A ";
		$sql .= PHP_EOL." WHERE A.address1 LIKE '$city%' ";

        $query = $this->db->query($sql) or $this->logsql->log_sql('error');
		$result = $query->row_array();

		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result['cnt'];
	}
}

/* End of file zipcode_model.php */
/* Location: ./application/models/member/zipcode_model.php */